# -*- coding:utf-8 -*-
from fastapi import Request, HTTPException
from sqlalchemy.exc import OperationalError
from sqlalchemy import insert, update, delete, select, func, text
from app.entity.system import SysRole, SysRoleMenu, CreateSysRole, UpdateSysRole, UpdateSysRoleDataScope
from core.curd import PageInfo, CRUDBase, IBaseDao
from typing import List
from core.constants import DBConstants

BaseDao = CRUDBase(SysRole)


class SysRoleDao(IBaseDao):
    """
    测试模块服务层
    """

    def __init__(self, req: Request):
        self.db_session = req.app.state.db_async_session

    async def select_by_id(self, ids: int):
        async with self.db_session() as db:
            try:
                data = await BaseDao.get(db, pk=ids)
                return data
            except OperationalError as e:
                raise HTTPException(status_code=500, detail='数据库服务连接失败!')
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))
            finally:
                await db.close()

    async def select_page(self, page: PageInfo, where: dict):
        async with self.db_session() as db:
            try:
                rs = await db.execute(select(func.count(SysRole.id)).filter_by(**where))
                total = rs.scalars().first()
                rows = []
                if total > 0:
                    rs = await db.execute(
                        select(SysRole).filter_by(**where).offset((page.page - 1) * page.limit).limit(page.limit))
                    rows = rs.scalars().all()
                return total, rows
            except OperationalError as e:
                raise HTTPException(status_code=500, detail='数据库服务连接失败!')
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))
            finally:
                await db.close()

    async def select_role_user(self, role_id: int, page: PageInfo, where: dict):
        async with self.db_session() as db:
            try:
                sql1 = "select count(*) from sys_user_role r, sys_user u where r.user_id = u.id and r.role_id = %d " % (
                    role_id)
                sql2 = "select r.role_id, r.user_id, u.username, u.real_name, u.email, u.mobile, u.gender from sys_user_role r, sys_user u where r.user_id = u.id and r.role_id = %d " % (
                    role_id)
                rs = await db.execute(text(sql1))
                total = rs.scalars().first()
                rows = []
                if total > 0:
                    rs = await db.execute(text(sql2))
                    rows = rs.fetchall()
                    rows = list(map(lambda x: {"role_id": x[0], "user_id": x[1], "username": x[2], "real_name": x[3],
                                               "email": x[4], "mobile": x[5], "gender": x[6]}, rows))

                return total, rows
            except OperationalError as e:
                raise HTTPException(status_code=500, detail=str(e))
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))
            finally:
                await db.close()

    async def insert(self, post: CreateSysRole, last_id: bool = False):
        async with self.db_session() as db:
            try:

                result = await db.execute(insert(SysRole).values(
                    **{"name": post.name, "role_code": post.role_code, "status": post.status, "remark": post.remark}))
                res = await db.execute(text("SELECT LAST_INSERT_ID() as last_id"))
                role_id = res.scalars().first()

                if role_id is not None:
                    for menu_id in post.menuIdList:
                        await db.execute(
                            text(f"insert into sys_role_menu (role_id, menu_id) values (%d, %d)" % (role_id, menu_id)))

            except OperationalError as e:
                raise HTTPException(status_code=500, detail='数据库服务连接失败!')
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))
            finally:
                await db.commit()
                await db.close()

    async def update(self, post: UpdateSysRole):
        async with self.db_session() as db:
            try:
                role = {"name": post.name, "role_code": post.role_code, "status": post.status, "remark": post.remark}
                result = await db.execute(update(SysRole).where(SysRole.id == post.id).values(**role))

                result = await db.execute(text("delete from sys_role_menu where role_id = %d" % post.id))

                for menu_id in post.menuIdList:
                    await db.execute(
                        text(f"insert into sys_role_menu (role_id, menu_id) values (%d, %d)" % (post.id, menu_id)))

            except OperationalError as e:
                raise HTTPException(status_code=500, detail='数据库服务连接失败!')
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))
            finally:
                await db.commit()
                await db.close()

    async def update_datascope(self, post: UpdateSysRoleDataScope):
        async with self.db_session() as db:
            try:
                role = {"data_scope": post.data_scope}
                result = await db.execute(update(SysRole).where(SysRole.id == post.id).values(**role))

                result = await db.execute(text("delete from sys_role_data_scope where role_id = %d" % post.id))

                for org_id in post.orgIdList:
                    await db.execute(
                        text(f"insert into sys_role_data_scope (role_id, org_id) values (%d, %d)" % (post.id, org_id)))

            except OperationalError as e:
                raise HTTPException(status_code=500, detail='数据库服务连接失败!')
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))
            finally:
                await db.commit()
                await db.close()

    async def delete_by_id(self, id):
        async with self.db_session() as db:
            try:
                rs = await BaseDao.delete_by_id(db, id)
                result = await db.execute(delete(SysRoleMenu).where(SysRoleMenu.role_id == id))

            except OperationalError as e:
                raise HTTPException(status_code=500, detail='数据库服务连接失败!')
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))
            finally:
                await db.commit()
                await db.close()

    async def delete_batch_ids(self, ids):
        async with self.db_session() as db:
            try:
                result = await db.execute(
                    text("delete from sys_role_menu where role_id in (%s)" % (",".join(map(str, ids)))))
                result = await db.execute(text("delete from sys_role where id in (%s)" % (",".join(map(str, ids)))))
            except OperationalError as e:
                raise HTTPException(status_code=500, detail='数据库服务连接失败!')
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))
            finally:
                await db.commit()
                await db.close()

    async def get_role_menu(self, id: int):
        async with self.db_session() as db:
            try:
                result = await db.execute(text("select menu_id from sys_role_menu where role_id = %d" % id))
                return result.scalars().all()

            except OperationalError as e:
                raise HTTPException(status_code=500, detail='数据库服务连接失败!')
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))
            finally:
                await db.close()

    async def get_role_org(self, id: int):
        async with self.db_session() as db:
            try:
                result = await db.execute(text("select org_id from sys_role_data_scope where role_id = %d" % id))
                return result.scalars().all()

            except OperationalError as e:
                raise HTTPException(status_code=500, detail='数据库服务连接失败!')
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))
            finally:
                await db.close()

    async def delete_role_user(self, role_id: int, user_ids: List[int]):
        async with self.db_session() as db:
            try:
                result = await db.execute(text("delete from sys_user_role where role_id=%d and user_id in (%s)" % (
                role_id, ",".join(map(str, user_ids)))))

            except OperationalError as e:
                raise HTTPException(status_code=500, detail='数据库服务连接失败!')
            except Exception as e:
                raise HTTPException(status_code=500, detail=str(e))
            finally:
                await db.commit()
                await db.close()

    async def select_options(self):
        data = await self.query(select(SysRole.id, SysRole.name).filter(SysRole.status == DBConstants.STATUS_OK))
        return list(map(lambda x: {"id": x[0], "name": x[1]}, data))

    @classmethod
    async def instance(cls, req: Request):
        return cls(req)
